﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ZHDJ.Data.Models.Common
{
    /// <summary>
    /// 积分申请
    /// </summary>
    public class PartyObjectScoreApply:EntityBase
    {
        #region 字段属性
        /// <summary>
        /// 获得积分的人/组织ID
        /// </summary>
        public string ObjectID { get; set; }

        /// <summary>
        /// Member人员，Branch组织
        /// </summary>
        public string ObjectType { get; set; }

        /// <summary>
        /// 加分类型（已不用）
        /// </summary>
        public string AddType { get; set; }

        /// <summary>
        /// 申请积分类型
        /// </summary>
        public string Category { get; set; }

        /// <summary>
        /// 标题
        /// </summary>
        
        public string Title { get; set; }

        /// <summary>
        /// 申报时间
        /// </summary>

        public DateTime? ApplyDate { get; set; }

        /// <summary>
        /// 说明
        /// </summary>

        public string Content { get; set; }

        /// <summary>
        /// 图片
        /// </summary>
        public string ImageID { get; set; }

        /// <summary>
        /// 申请成功获得的积分
        /// </summary>
        
        public decimal? Score { get; set; }

        /// <summary>
        /// 状态 1草稿，2审核中，4审核通过，8驳回，3支部审核通过，5支部审核驳回，7党委审核通过，9党委审核驳回
        /// </summary>
        
        public int? Status { get; set; }
        /// <summary>
        /// 审核意见
        /// </summary>
        public string AduitContent { get; set; }

        /// <summary>
        /// 创建时间
        /// </summary>
        public DateTime? CreateTime { get; set; }

        /// <summary>
        /// 创建人
        /// </summary>
        public string CreateBy { get; set; }

        /// <summary>
        /// 修改时间
        /// </summary>
        public DateTime? ModifyTime { get; set; }

        /// <summary>
        /// 修改人
        /// </summary>
        public string ModifyBy { get; set; }


        #endregion


        /// <summary>
        /// 组织积分申请查询(超级管理员,查询全部)
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="category"></param>
        /// <param name="searchKey"></param>
        /// <returns></returns>
        public static DataSet GetGridDataBranch(int pageIndex, int pageSize, string category, string searchKey = "")
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "Begin",Value = (pageIndex)*pageSize},
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "PageSize",Value = pageSize}
            };
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select SQL_CALC_FOUND_ROWS  
                        data.ID,data.ObjectID,data.ObjectType,data.AddType,data.Category,data.Title
                        ,data.ApplyDate,data.Content,data.ImageID,data.Score
                        ,case data.Status when 2 then '审核中' when 4 then '审核通过' when 8 then '驳回' end as Status
                        ,data.AduitContent,data.CreateTime,data.CreateBy,data.ModifyTime,data.ModifyBy
                        ,b.BranchName as Name,m2.UserName CreateByName,r.Name CategoryName,
                        case data.ObjectType when 'Branch' then '组织积分' else '个人积分' end as  ObjectTypeName
                        from tblPartyObjectScoreapply data
                        inner join tblpartyorgbranch b on (b.branchid=data.objectid) 
                        inner join tblpartyorgmember m2 on(m2.LoginID= data.CreateBy)
                        inner join tblPartyObjectScoreRule r on(r.objectType='Branch' and r.Category=data.Category)
                        where   data.objectType='Branch' ");
            if (!string.IsNullOrEmpty(searchKey))
            {
                sb.Append(" and b.BranchName like ?key ");
                parameters.Add(
                    new MySqlParameter() { DbType = DbType.String, ParameterName = "key", Value = string.Format("%{0}%", searchKey) });
            }

            if (!string.IsNullOrEmpty(category))
            {
                sb.Append(" and data.Category=?Category");
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "Category", Value = category });
            }

            sb.Append(" order by data.CreateTime desc LIMIT ?Begin ,?PageSize;SELECT FOUND_ROWS();");
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());
        }


        /// <summary>
        /// 组织积分申请查询(组织管理员查询驳回信息)
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="category"></param>
        /// <param name="searchKey"></param>
        /// <returns></returns>
        public static DataSet GetGridDataBranch1(int pageIndex, int pageSize, string category, string BranchID, string searchKey = "")
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "Begin",Value = (pageIndex)*pageSize},
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "PageSize",Value = pageSize}
            };
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select SQL_CALC_FOUND_ROWS  
                        data.ID,data.ObjectID,data.ObjectType,data.AddType,data.Category,data.Title
                        ,data.ApplyDate,data.Content,data.ImageID,data.Score
                        ,case data.Status when 4 then '审核通过' when 8 then '驳回' else '审核中' end as Status
                        ,data.AduitContent,data.CreateTime,data.CreateBy,data.ModifyTime,data.ModifyBy
                        ,b.BranchName as Name,m2.UserName CreateByName,r.Name CategoryName,
                        case data.ObjectType when 'Branch' then '组织积分' else '个人积分' end as  ObjectTypeName
                        from tblPartyObjectScoreapply data
                        inner join tblpartyorgbranch b on (b.branchid=data.objectid) 
                        inner join tblpartyorgmember m2 on(m2.LoginID= data.CreateBy)
                        inner join tblPartyObjectScoreRule r on(r.objectType='Branch' and r.Category=data.Category)
                        where   data.objectType='Branch' ");
            if (!string.IsNullOrEmpty(searchKey))
            {
                sb.Append(" and b.BranchName like ?key ");
                parameters.Add(
                    new MySqlParameter() { DbType = DbType.String, ParameterName = "key", Value = string.Format("%{0}%", searchKey) });
            }

            if (!string.IsNullOrEmpty(category))
            {
                sb.Append(" and data.Category=?Category");
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "Category", Value = category });
            }
            if (BranchID != "")
            {
                sb.Append(" and data.ObjectID='" + BranchID + "'");
            }
            sb.Append(" order by data.CreateTime desc LIMIT ?Begin ,?PageSize;SELECT FOUND_ROWS();");
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());
        }


        /// <summary>
        /// 党务工作者查询个人积分
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="category"></param>
        /// <param name="searchKey"></param>
        /// <returns></returns>
        public static DataSet GetGridDataMember(int pageIndex, int pageSize, string category, string searchKey = "",string branchid="", string loginId = "", string bm="")
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "Begin",Value = (pageIndex)*pageSize},
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "PageSize",Value = pageSize}
            };
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select SQL_CALC_FOUND_ROWS  
                        data.ID,data.ObjectID,data.ObjectType,data.AddType,data.Category,data.Title
                        ,data.ApplyDate,data.Content,data.ImageID,data.Score
                        ,case data.Status when 4 then '审核通过' when 8 then '驳回' 
                        when 3 then '党委审核' when 5 then '支部审核驳回' when 7 then '最终审核' when 9 then '党委审核驳回' 
                        when 2 then '支部审核' 
                        else '审核中' end as Status
                        ,data.AduitContent,data.CreateTime,data.CreateBy,data.ModifyTime,data.ModifyBy
                        ,b.UserName as Name,m2.UserName CreateByName,r.Name CategoryName,
                        case data.ObjectType when 'Branch' then '组织积分' else '个人积分' end as  ObjectTypeName
                        from tblPartyObjectScoreapply data
                        inner join tblpartyorgmember b on (b.LoginID= data.ObjectID) 
                        inner join tblpartyorgmember m2 on(m2.LoginID= data.CreateBy)
                        inner join tblPartyObjectScoreRule r on(r.objectType='Member' and r.Category=data.Category)
                        where   data.objectType='Member'  ");
            if (!string.IsNullOrEmpty(searchKey))
            {
                sb.Append(" and data.Title like ?key ");
                parameters.Add(
                    new MySqlParameter() { DbType = DbType.String, ParameterName = "key", Value = string.Format("%{0}%", searchKey) });
            }

            if (!string.IsNullOrEmpty(category))
            {
                sb.Append(" and data.Category=?Category");
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "Category", Value = category });
            }
            if (!string.IsNullOrEmpty(branchid))
            {
                //sb.Append(" and b.BranchID in (select BranchID from tblpartyorgbranch a where a.ParentBranchID==?ObjectId) or a.BranchID=?ObjectId or a.ParentBranchID in(select BranchID from tblpartyorgbranch a where a.ParentBranchID=?ObjectId");
                sb.Append(" and b.BranchID in (select childbranchid from tblpartyorgbranchrelation a where a.BranchID=?ObjectId)");
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "ObjectId", Value = branchid });
            }
            if (bm == "1") {
                sb.Append(" and (data.Status=7 or data.ObjectId='" + loginId + "')");
            }
            if (bm == "2")
            {
                sb.Append(" and (data.Status=3 or data.ObjectId='" + loginId + "')");
            }
            if (bm == "3")
            {
                sb.Append(" and (data.Status=2 or data.Status=0 or data.ObjectId='" + loginId + "')");
            }
            if (bm == "")
            {
                sb.Append(" and (data.Status=2 or data.Status=0 or data.ObjectId='" + loginId + "')");
            }
            sb.Append(" order by data.CreateTime desc LIMIT ?Begin ,?PageSize;SELECT FOUND_ROWS();");
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());
        }



        /// <summary>
        /// 个人积分查询
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="category"></param>
        /// <param name="searchKey"></param>
        /// <returns></returns>
        public static DataSet GetGridDataMember1(int pageIndex, int pageSize, string category, string searchKey = "", string loginId = "")
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "Begin",Value = (pageIndex)*pageSize},
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "PageSize",Value = pageSize}
            };
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select SQL_CALC_FOUND_ROWS  
                        data.ID,data.ObjectID,data.ObjectType,data.AddType,data.Category,data.Title
                        ,data.ApplyDate,data.Content,data.ImageID,data.Score
                        ,case data.Status when 4 then '审核通过' when 8 then '驳回' 
                        when 3 then '党委审核' when 5 then '支部审核驳回' when 7 then '最终审核' when 9 then '党委审核驳回'
                        when 2 then '支部审核' 
                        else '审核中' end as Status
                        ,data.AduitContent,data.CreateTime,data.CreateBy,data.ModifyTime,data.ModifyBy
                        ,b.UserName as Name,m2.UserName CreateByName,r.Name CategoryName,
                        case data.ObjectType when 'Branch' then '组织积分' else '个人积分' end as  ObjectTypeName
                        from tblPartyObjectScoreapply data
                        inner join tblpartyorgmember b on (b.LoginID= data.ObjectID) 
                        inner join tblpartyorgmember m2 on(m2.LoginID= data.CreateBy)
                        inner join tblPartyObjectScoreRule r on(r.objectType='Member' and r.Category=data.Category)
                        where   data.objectType='Member'  ");
            if (!string.IsNullOrEmpty(searchKey))
            {
                sb.Append(" and m.UserName like ?key ");
                parameters.Add(
                    new MySqlParameter() { DbType = DbType.String, ParameterName = "key", Value = string.Format("%{0}%", searchKey) });
            }

            if (!string.IsNullOrEmpty(category))
            {
                sb.Append(" and data.Category=?Category");
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "Category", Value = category });
            }
            if (!string.IsNullOrEmpty(loginId))
            {
                sb.Append(" and data.ObjectId=?ObjectId");
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "ObjectId", Value = loginId });
            }
            sb.Append(" order by data.CreateTime desc LIMIT ?Begin ,?PageSize;SELECT FOUND_ROWS();");
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());
        }
    }
}
